package com.bsh.repository;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.bsh.mybatis.IRepository.CloudReferralsMapper;
import com.bsh.mybatis.model.CloudReferrals;
import com.bsh.utils.Pager;
import com.mysql.jdbc.StringUtils;

@Repository
public class DaoCloudReferrals {
	@Resource
	SqlSessionTemplate session;
	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;

	CloudReferralsMapper getMapper() {
		return session.getMapper(CloudReferralsMapper.class);
	}

	public int deleteByPrimaryKey(Integer id) {
		int res = getMapper().deleteByPrimaryKey(id);
		return res;
	}

	public int insert(CloudReferrals record) {
		int res = getMapper().insert(record);
		return record.getId();
	}

	public int insertSelective(CloudReferrals record) {
		int res = getMapper().insertSelective(record);
		return record.getId();
	}

	public CloudReferrals selectByPrimaryKey(Integer id) {
		CloudReferrals row = getMapper().selectByPrimaryKey(id);
		return row;
	}

	public int updateByPrimaryKeySelective(CloudReferrals record) {
		int res = getMapper().updateByPrimaryKeySelective(record);
		return res;
	}

	public int updateByPrimaryKey(CloudReferrals record) {
		int res = getMapper().updateByPrimaryKey(record);
		return res;
	}

	/**
	 * 邀请返利分页查询
	 */
	public List<Map<String, Object>> getReferralsPage(Pager pager, String where) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		sb.append("select * from cloud_referrals ");
		sb.append("where 1=1 ");

		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);

		sb.append(" order by id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}

	/**
	 * 邀请返利统计分页查询
	 */
	public List<Map<String, Object>> getReferralsCountPage(Pager pager, String where) {
		MapSqlParameterSource ps = new MapSqlParameterSource();

		StringBuilder sb = new StringBuilder();
		// sb.append("select v.* from (");
		// sb.append("select *,(select count(0) from user u2 where u2.pid=u.id)
		// rel_count from user u ");
		// sb.append(" )v ");
		// sb.append(" where v.rel_count>0 ");

		sb.append("SELECT u.mobile,u.real_name,u.name,t.c rel_count ");
		sb.append(" from (select pid,count(0) as c from user ");
		sb.append("	group by pid ");
		sb.append("HAVING pid is not null ");
		sb.append("	order by c DESC) t ");
		sb.append("LEFT JOIN user u on t.pid= u.id ");
		sb.append(" where 1=1 ");

		if (!StringUtils.isNullOrEmpty(where)) {
			sb.append(where);
		}
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sb.toString() + ")v", ps);
		sb.append(" order by id desc limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sb.toString(), ps);

		pager.setRowCount(count);
		return list;
	}
}
